const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🗑️ 删除无效的实例表');
console.log('=======================');

try {
  const invalidTableName = 'device_instances_不存在_0000';
  
  // 1. 检查表是否存在
  const tableExists = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name = ?
  `).get(invalidTableName);
  
  if (!tableExists) {
    console.log(`❌ 表 ${invalidTableName} 不存在`);
    return;
  }
  
  console.log(`✅ 找到无效表: ${invalidTableName}`);
  
  // 2. 检查表中是否有数据
  const recordCount = db.prepare(`SELECT COUNT(*) as count FROM ${invalidTableName}`).get();
  console.log(`📊 表中记录数: ${recordCount.count}`);
  
  // 3. 删除表
  console.log('🗑️ 正在删除表...');
  db.prepare(`DROP TABLE IF EXISTS ${invalidTableName}`).run();
  
  // 4. 验证删除结果
  const afterDelete = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name = ?
  `).get(invalidTableName);
  
  if (!afterDelete) {
    console.log(`✅ 成功删除表: ${invalidTableName}`);
  } else {
    console.log(`❌ 删除失败: ${invalidTableName}`);
  }
  
  // 5. 统计剩余实例表数量
  const remainingTables = db.prepare(`
    SELECT COUNT(*) as count FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
  `).get();
  
  console.log(`📊 剩余实例表数量: ${remainingTables.count}`);
  
} catch (error) {
  console.error('❌ 删除失败:', error.message);
} finally {
  db.close();
}